{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "848523b1-cb88-4fb8-a450-dd26f44c2f6a",
   "metadata": {},
   "source": [
    "# Exercise 4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24c1bd7b-a907-49d9-b49b-77be709131bb",
   "metadata": {},
   "source": [
    "## Question 1\n",
    "Create tables to keep track of trading positions\n",
    "\n",
    "**Hint**\n",
    "\n",
    "After each trade, we want to keep at least the following information of each traded instrument:\n",
    "1. the date and time when the trade takes place\n",
    "2. the name of the instrument traded\n",
    "3. the quantity of the instrument that we possess after the trade\n",
    "4. The amount of cash available to us for trading the instrument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a5325b0c-f961-486d-9e98-fdd3a808886c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "from contextlib import closing\n",
    "\n",
    "conn = None\n",
    "with closing(sqlite3.connect(\"SP500.db\")) as conn:\n",
    "    # Your code goes here"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "637907f8-0e41-4cf7-ae4b-2d0d192fefae",
   "metadata": {},
   "source": [
    "## Question 2\n",
    "Choose a lookback time of 120 days and perform backtesting on the prices from 2021-05-02 to 2021-05-31.\n",
    "\n",
    "**hint**\n",
    "\n",
    "1. On each day from 2021-05-02 to 2021-05-31, take the latest 120 days' prices before this date and calibrate a GBM model.\n",
    "2. Use the GBM model to forecast the price in 10 days:\n",
    "   $$S_{t + n\\Delta t} = S_t \\exp \\left[\n",
    "   \\left(\n",
    "   \\mu - \\frac{\\sigma^2}{2}\n",
    "   \\right)n \\Delta t\n",
    "   + W_{t + n \\Delta t} - W_t\n",
    "   \\right]$$\n",
    "The price forecast is\n",
    "$$\n",
    "\\mathbb E S_{t + n\\Delta t} = S_t \\exp \\left(\n",
    "   \\mu n \\Delta t\n",
    "\\right)\n",
    "$$\n",
    "In this case, $n=10, \\Delta t = 1/250$. Use your code from question 3 of exercise 3 to estimate the confidence interval of the forecast."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83be7ef8-cbb2-4226-95ac-7abd3cd12b53",
   "metadata": {},
   "source": [
    "3. The relative return of buying the instrument on day 0 and selling it on day 10 is approximately normally distributed with mean $(\\mu - \\frac{\\sigma^2}{2}) n \\Delta t$ and standard deviation $\\sigma \\sqrt{n \\Delta t}$. Calculate the 95% expected shortfall $ES_\\alpha$ in 10 days:\n",
    "$$ES_\\alpha = -m + s \\frac{\\phi(\\Phi^{-1}(\\alpha))}{1 - \\alpha}$$\n",
    "where $\\alpha = 0.95$ and\n",
    "$$m = \\left(\\mu - \\frac{\\sigma^2}{2} \\right) n \\Delta t$$\n",
    "$$s = \\sigma \\sqrt{n \\Delta t}$$\n",
    "$\\phi$ and $\\Phi^{-1}$ are the density and the quantile functions of the standard normal distribution, respectively. You can calculate them in Python using scipy.stats.norm.pdf and scipy.stats.norm.ppf, respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d90334aa-96d9-43c8-8de0-d6f4c9c42714",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from scipy.stats import norm\n",
    "mu = 0.0511\n",
    "sigma = 0.1440\n",
    "alpha = 0.95\n",
    "# Your code goes here\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c82b8c45-d16d-4f5b-8b84-6742ba30d6cf",
   "metadata": {},
   "source": [
    "4. Devise your buy/sell signal based on the forecasted price and its confidence interval. For example, if your forecast suggests that you can be 80% sure that the price in 10 days will be higher than the current price, buy the instrument.\n",
    "5. Decide your risk appetite in terms of the *Expected Shortfall*. For example, if you decide that your risk appetite is 95% expected shortfall being 5%, then the ratio of capital that you should use for the trade is $\\frac{0.05}{ES_{0.95}}$. If this figure is larger than 1, you may consider using leverage.\n",
    "6. Follow your buy/sell signals through the said time period. Store in the database the number of the instruments that you hold and the amount of your cash reserve after each trade."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd7a93dc-e097-4384-82d5-7c1d082c3f26",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
